﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Data.Sqlite;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace IceCS.Dapper.Core
{
    public class DapperHelper
    {
        private static int _instanceId = 0;
        /// <summary>
        /// 实例id
        /// </summary>
        public int Id { get; } = 0;

        private DapperHelper _default;
        /// <summary>
        /// 默认实例对象，可以作为实例挂载点
        /// </summary>
        public DapperHelper Default
        {
            get
            {
                return _default;
            }
            set
            {
                _default = value;
            }
        }
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString { get; set; }

        /// <summary>
        /// 数据库类型
        /// </summary>
        public DBCategory DBCategory { get; set; }

        /// <summary>
        /// 
        /// </summary>
        /// 
        public DapperHelper(string connectionString, DBCategory category)
        {
            this.ConnectionString = connectionString;
            this.DBCategory = category;
            this.Id = ++_instanceId;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="category"></param>
        /// <returns></returns>
        public IDbConnection GetConnection()
        {
            if (string.IsNullOrWhiteSpace(ConnectionString))
            {
                throw new Exception("请配置连接字符串");
            }
            switch (DBCategory)
            {
                case DBCategory.Sqlite3:
                    return new SqliteConnection(ConnectionString);
                case DBCategory.MySql:
                    return new MySqlConnection(ConnectionString);
                case DBCategory.MsSql:
                    return new SqlConnection(ConnectionString);
                case DBCategory.Oracle:
                    return new OracleConnection(ConnectionString);
                default:
                    return new SqliteConnection(ConnectionString);
            }
        }

        /// <summary>
        /// 判断数据库是否连接上
        /// </summary>
        /// <returns></returns>
        public bool IsConnect()
        {
            bool result = false;
            UseDbConnection(conn =>
            {
                result = conn.State == ConnectionState.Open;
            });
            return result;
        }

        /// <summary>
        /// 使用数据库连接
        /// </summary>
        /// <param name="action"></param>
        public void UseDbConnection(Action<IDbConnection> action)
        {
            using (IDbConnection conn = GetConnection())
            {
                try
                {
                    conn.Open();
                    action(conn);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("DB Exception:" + ex);
                }

            }
        }

        /// <summary>
        /// 使用数据库连接
        /// </summary>
        /// <param name="action"></param>
        public TResult UseDbConnection<TResult>(Func<IDbConnection, TResult> action)
        {
            using (IDbConnection conn = GetConnection())
            {
                try
                {
                    conn.Open();
                    return action(conn);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("DB Exception:" + ex);
                }
            }
            return default;
        }

        /// <summary>
        /// 使用数据库连接,事务包裹，自动提交事务
        /// </summary>
        /// <param name="action"></param>
        public void UseDbConnectionWithTtransaction(Action<IDbConnection> action)
        {
            IDbTransaction transaction;
            using (IDbConnection conn = GetConnection())
            {
                conn.Open();
                transaction = conn.BeginTransaction();
                try
                {
                    action(conn);
                    transaction.Commit();
                }
                catch (DBConcurrencyException ex)
                {
                    Console.WriteLine("DB Concurrency Exception:" + ex);
                    transaction.Rollback();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("DB Exception:" + ex);
                    transaction.Rollback();
                }
            }
        }

        /// <summary>
        /// 使用数据库连接,事务包裹，自动提交事务
        /// </summary>
        /// <param name="action"></param>
        public TResult UseDbConnectionWithTtransaction<TResult>(Func<IDbConnection, TResult> action)
        {
            IDbTransaction transaction;
            using (IDbConnection conn = GetConnection())
            {
                conn.Open();
                transaction = conn.BeginTransaction();
                try
                {
                    var result = action(conn);
                    transaction.Commit();
                    return result;
                }
                catch (DBConcurrencyException ex)
                {
                    Console.WriteLine("DB Concurrency Exception:" + ex);
                    transaction.Rollback();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("DB Exception:" + ex);
                    transaction.Rollback();
                }
            }
            return default;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<TResult> Query<TResult>(string sql, object param)
        {
            return UseDbConnection(conn =>
            {
                return conn.Query<TResult>(sql, param);
            }).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<dynamic> Query(string sql, object param)
        {
            return UseDbConnection(conn =>
            {
                return conn.Query(sql, param);
            }).ToList();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public dynamic QueryOne(string sql, object param)
        {
            return Query(sql, param).FirstOrDefault();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public int Execute(string sql, object param)
        {
            return UseDbConnection(conn =>
            {
                return conn.Execute(sql, param);
            });
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public TResult ExecuteScalar<TResult>(string sql, object param)
        {
            return UseDbConnection(conn =>
            {
                return conn.ExecuteScalar<TResult>(sql, param);
            });
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, object param)
        {
            return UseDbConnection(conn =>
            {
                return conn.ExecuteScalar(sql, param);
            });
        }
    }
}
